#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3

import datetime
import time
import calendar
import os.path


import exputils

class ExpensesDatabase(exputils.AbstractNotifier):
    EXPENSE = 0
    EARNING = 1
    
    def __init__(self, db_name):
        exputils.AbstractNotifier.__init__(self)
        self._delta = datetime.timedelta(days=1)
        self._current_day = None
        self._db_name = db_name
        self._open_database()
    
    def _open_database(self):
        sqlite3.register_converter('date', self._converter_date)
        sqlite3.register_adapter(datetime.date, self._adapter_data)
        create_database = not os.path.exists(self._db_name)
        self.connection = sqlite3.connect(self._db_name, detect_types=sqlite3.PARSE_DECLTYPES)
        self.connection.isolation_level = None
        self.connection.text_factory = str
        if create_database:
            self.connection.execute("""create table operations(day date not null,
                                                               value real not null,
                                                               description text not null,
                                                               type integer not null,
                                                               tag integer)""")
            self.connection.execute("""create table tags(name text not null,
                                                         description text,
                                                         color integer)""")
        
    def import_from_v10(self, old):
        if not os.path.exists(old):
            print 'Old database not found!'
            return
        old_conn = sqlite3.connect(old, detect_types=sqlite3.PARSE_DECLTYPES)
        old_conn.isolation_level = None
        old_conn.text_factory = str
        
        for table in (('expenses', ExpensesDatabase.EXPENSE), ('earnings', ExpensesDatabase.EARNING)):
            print '\tCopying dat from %s...' % table[0],
            old_cursor = old_conn.execute('select day, value, description from %s' % table[0])
            for row in old_cursor:
                self.add_position(row[1], row[2], table[1], None, row[0])
            print 'done.'
        print 'All done.'
        
    
    def set_next_day(self):
        self.set_day(self._current_day + self._delta)
    
    def set_prev_day(self):
        self.set_day(self._current_day - self._delta)
    
    def set_day(self, day):
        if self._current_day == day:
            return
        self._current_day = day
        self._notify_listeners('day_changed', self._current_day)
    
    def get_day(self):
        return self._current_day
    
    def add_position(self, value, description, type, tag=None, day=None):
        if day == None:
            day = self._current_day
        rowid = self.connection.execute('insert into operations values(?, ?, ?, ?, ?)', (day, value, description, type, tag)).lastrowid
        self._notify_listeners('data_changed')
        return rowid
    
    def update_position(self, rowid, value, description, type, tag, day):
        self.connection.execute('update operations set day=?, value=?, description=?, type=?, tag=? where ROWID=?', (day, value, description, type, tag, rowid))
        self._notify_listeners('data_changed')
    
    def remove_position(self, rowid):
        rowcount = self.connection.execute('delete from operations where ROWID=?', (rowid, )).rowcount
        if rowcount > 0:
            self._notify_listeners('data_changed')
    
    def get_position(self, rowid):
        return self.connection.execute('select day, value, description, type, tag from operations where ROWID=?', (rowid, )).fetchone()
    
    def get_positions_by_day(self, day=None):
        if day == None:
            day = self._current_day
        data = { self.EARNING: 0, self.EXPENSE: 0}
        for type in (ExpensesDatabase.EXPENSE, ExpensesDatabase.EARNING):
            cursor = self.connection.execute('select ROWID, value, description, type, tag from operations where day=? and type=?', (day, type))
            data[type] = [ row for row in cursor]
        return data
    
    def get_day_summary(self, day=None):
        if day == None:
            day = self._current_day
        data = { self.EARNING: 0, self.EXPENSE: 0}
        cursor = self.connection.execute('select type, sum(value) from operations where day=?', (day,))
        for row in cursor:
            data[row[0]] = row[1]
        data['total'] = data[self.EARNING] - data[self.EXPENSE]
        return data
    
    def get_month_summary(self, day=None):
        if day == None:
            day = self._current_day
        first_day = datetime.date(day.year, day.month, 1)
        last_day  = datetime.date(day.year, day.month, calendar.mdays[day.month])
        return self.get_summary(first_day, last_day)
    
    def get_summary(self, start, end):
        delta = end - start
        data = { self.EARNING: {'sum': 0, 'avg': 0}, self.EXPENSE: {'sum': 0, 'avg': 0}}
        cursor = self.connection.execute('select type, sum(value) from operations where day between ? and ? group by type', (start, end))
        for row in cursor:
            if row[0] == None:
                continue
            data[row[0]]['sum'] = row[1]
            data[row[0]]['avg'] = row[1] / (delta.days + 1)
        data['total'] = data[self.EARNING]['sum'] - data[self.EXPENSE]['sum']
        return data
    """
    def add_item(self, value, description, date=None):
        if date == None:
            date = self._current_day
        if value > 0:
            table = 'earnings'
        else:
            table = 'expenses'
            value = -value
        rowid = self.connection.execute('insert into %s values(?, ?, ?)' % table, (date, value, description)).lastrowid
        self._notify_listeners('data_changed')
        return (table, rowid)
    
    def update_item(self, rowid, value, description):
        self.connection.execute('update %s set value=?, description=?, day=? where ROWID=?' % rowid[0], (value, description, self._current_day, rowid[1])).lastrowid
        self._notify_listeners('data_changed')
    
    def remove_item(self, rowid):
        self.connection.execute('delete from %s where ROWID=?' % rowid[0], (rowid[1], )).rowcount
        self._notify_listeners('data_changed')

    def get_item(self, rowid):
        return self.connection.execute('select value, description from %s where ROWID=?' % rowid[0], (rowid[1], )).fetchone()
    
    def get_items(self):
        data = { }
        for table in ('expenses', 'earnings'):
            cursor = self.connection.execute('select \'%s\', ROWID, value, description from %s where day=?' % (table, table), (self._current_day,))
            data[table] = ([ row for row in cursor])
        return data
    
    def get_day_info(self):
        data = { }
        for table in ('expenses', 'earnings'):
            sum = self.connection.execute('select sum(value) from %s where day=?' % table, (self._current_day,)).fetchone()[0]
            if sum == None:
                sum = 0
            data[table] = sum
        data['total'] = data['earnings'] - data['expenses']
        return data
    
    def get_month_info(self, date=None):
        if not date:
            date = self._current_day
        start_date  = datetime.date(date.year, date.month, 1)
        end_date    = datetime.date(date.year, date.month, calendar.mdays[date.month])
        day_no      = datetime.date.today().day
        data = { }
        for table in ('expenses', 'earnings'):
            data[table]= self.connection.execute('select sum(value) from %s where day between ? and ?' % table, (start_date, end_date)).fetchone()[0]
            if data[table] == None:
                data[table] = 0
        data['total'] = data['earnings'] - data['expenses']
        data['avg_expenses'] = data['expenses'] / day_no
        return data
    """
    def _converter_date(self, date):
        try:
            return datetime.date.fromtimestamp(int(date))
        except:
            return ''
    
    def _adapter_data(self, date):
        try:
            return time.mktime(date.timetuple())
        except:
            return None
